package cn.ztydata.teachingsystem.heaven.dao;

import cn.ztydata.teachingsystem.heaven.cons.CourseOverview;
import cn.ztydata.teachingsystem.heaven.cons.MsgTemplate;
import cn.ztydata.teachingsystem.heaven.entity.CourseProfile;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.MessageFormat;
import java.util.Map;

/**
 * 课程概况dao
 * Created by chenxin on 2014/12/10.
 */
@Repository
public class CourseOverviewDao extends BaseDao {
    /**
     * 查询指定类型的课程概况
     * @param courseId 课程ID
     * @param type 概况类型
     * @return String
     *
     * @author cx
     * @since 2014-12-10
     */
    public CourseProfile getContent(final long courseId, final int type) {
        //文本内容
        final String field = judgeFied(type);
        //文件下载
        final String downloadField = judgeDownloadFied(type);
        //文件显示
        final String showField = judgeShowFied(type);

        if (field == null) {
            log.debug("不存在的课程概况类型:field == null");
            return null;
        }

        String sql = "SELECT " + field + "," + downloadField + "," + showField + " FROM t_course_overview WHERE iCourseId=:courseId";

        try {
            SqlParameterSource sps = new MapSqlParameterSource().addValue("field", field).addValue("courseId", courseId);

            log.debug("查询指定类型的课程概况");
            return namedParameterJdbcTemplate.queryForObject(sql, sps, new RowMapper<CourseProfile>() {
                @Override
                public CourseProfile mapRow(ResultSet resultSet, int i) throws SQLException {
                    CourseProfile profile = new CourseProfile();

                    profile.setId(courseId);
                    profile.setType(type);
                    profile.setContent(resultSet.getString(field));
                    profile.setShowUrl(resultSet.getString(showField));
                    profile.setDownloadUrl(resultSet.getString(downloadField));

                    return profile;
                }
            });
        } catch (EmptyResultDataAccessException ex) {
            log.debug("未查到课程的概况信息");
            log.warn(MessageFormat.format(MsgTemplate.RECORD_NOT_FOUND, "courseId", courseId));
            return null;
        }
    }

    /**
     * 更新课程概况内容
     * @param courseId 课程ID
     * @param type 概况类型
     * @param content 概况内容
     * @return boolean
     *
     * @author cx
     * @since 2014-12-10
     */
    public boolean updateContent(final long courseId, final int type, final String content) {
        String field = judgeFied(type);
        if (field == null) {
            log.debug("不存在的课程概况类型:field == null");
            return false;
        }

        String sql = "UPDATE t_course_overview SET " + field + "=:content WHERE iCourseId=:courseId";

        SqlParameterSource sps = new MapSqlParameterSource().addValue("field", field)
                .addValue("content", content)
                .addValue("courseId", courseId);

        log.debug("更新课程概况内容");
        return namedParameterJdbcTemplate.update(sql, sps) == 1;
    }

    /**
     * 更新课程概况文件
     * @param courseId 课程ID
     * @param type 概况类型
     * @param url 概况文件
     * @return boolean
     *
     * @author fanruiping
     * @since 2015-02-05
     */
    public boolean updateFile(final long courseId, final int type, final String url) {
        String field = judgeDownloadFied(type);
        if (field == null) {
            log.debug("不存在的课程概况类型:field == null");
            return false;
        }

        String sql = "UPDATE t_course_overview SET " + field + "=:file WHERE iCourseId=:courseId";

        SqlParameterSource sps = new MapSqlParameterSource().addValue("field", field)
                .addValue("file", url)
                .addValue("courseId", courseId);

        log.debug("更新课程概况内容");
        return namedParameterJdbcTemplate.update(sql, sps) == 1;
    }

    /**
     * 清空概况下载/显示地址
     * @param id
     * @param type
     * @return boolean
     *
     * @author fanruiping
     * @since 2015-02-05
     */
    public boolean deleteFile(final long id, final int type){
        String downloadFile = judgeDownloadFied(type);
        String showFile = judgeShowFied(type);

        if (downloadFile == null) {
            log.debug("不存在的课程概况类型:field == null");
            return false;
        }

        String sql = "UPDATE t_course_overview SET " + downloadFile + "='',"+showFile+"='' WHERE iCourseId=:id";

        SqlParameterSource sps = new MapSqlParameterSource().addValue("field", downloadFile)
                .addValue("id", id);

        log.debug("更新课程概况内容");
        return namedParameterJdbcTemplate.update(sql, sps) == 1;
    }

    /**
     * 保存课程概况记录
     * @param courseId 课程ID
     * @return boolean
     *
     * @author cx
     * @since 2014-12-11
     */
    public boolean save(final long courseId) {
        String sql = "INSERT INTO t_course_overview(iCourseId) VALUES(:courseId)";

        log.debug("保存课程概况记录");
        return namedParameterJdbcTemplate.update(sql, new MapSqlParameterSource().addValue("courseId", courseId)) == 1;
    }

    /**
     * 获取概况字段是否为空
     *
     * @author chenxin
     * @since 2014/12/17
     */
    public Map<String, Object> getForNull(final long courseId) {
        StringBuilder sql = new StringBuilder("SELECT ");

        sql.append("(sAbstract IS NOT NULL && TRIM(sAbstract)<>'') OR (sAbstractDownloadUrl IS NOT NULL && TRIM(sAbstractDownloadUrl)<>'') AS '1'")
        .append(",(sTeachingOutline IS NOT NULL && TRIM(sTeachingOutline)<>'') OR (sOutlineDownloadUrl IS NOT NULL && TRIM(sOutlineDownloadUrl)<>'') AS '2'")
        .append(",(sTeachingCalendar IS NOT NULL && TRIM(sTeachingCalendar)<>'') OR (sCalendarDownlloadUrl IS NOT NULL && TRIM(sCalendarDownlloadUrl)<>'') AS '3'")
        .append(",(sStudyGuide IS NOT NULL && TRIM(sStudyGuide)<>'') OR (sGuideDownloadUrl IS NOT NULL && TRIM(sGuideDownloadUrl)<>'') AS '4'")
        .append(",(sTeachingForcus IS NOT NULL && TRIM(sTeachingForcus)<>'') OR (sForcusDownloadUrl IS NOT NULL && TRIM(sForcusDownloadUrl)<>'') AS '5'")
        .append(",(sReference IS NOT NULL && TRIM(sReference)<>'') OR (sReferenceDownloadUrl IS NOT NULL && TRIM(sReferenceDownloadUrl)<>'') AS '6'")
        .append(",(sAppraisal IS NOT NULL && TRIM(sAppraisal)<>'') OR (sAppraisalDownloadUrl IS NOT NULL && TRIM(sAppraisalDownloadUrl)<>'') AS '7'")
        .append(",(sDeclaration IS NOT NULL && TRIM(sDeclaration)<>'') OR (sDeclarationDownloadUrl IS NOT NULL && TRIM(sDeclarationDownloadUrl)<>'') AS '8'");

        sql.append(" FROM t_course_overview WHERE iCourseId=:courseId");

        try {
            log.debug("获取概况字段是否为空");
            return namedParameterJdbcTemplate.queryForMap(sql.toString(), new MapSqlParameterSource().addValue("courseId", courseId));
        } catch (EmptyResultDataAccessException ex) {
            log.debug("未找到指定课程的概况字段");
            log.warn(MessageFormat.format(MsgTemplate.RECORD_NOT_FOUND, "courseId", courseId));
            return null;
        }
    }

    /**
     * 判断概况字段
     * @param type 概况类型
     * @return String
     *
     * @author cx
     * @since 2014-12-10
     */
    private String judgeFied(final int type) {
        switch (type) {
            case CourseOverview.ABSTRACT: //课程简介
                log.debug("类型->课程简介");
                return "sAbstract";
            case CourseOverview.TEACHING_OUTLINE: //教学大纲
                log.debug("类型->教学大纲");
                return "sTeachingOutline";
            case CourseOverview.TEACHING_CALENDAR: //教学日历
                log.debug("类型->教学日历");
                return "sTeachingCalendar";
            case CourseOverview.STUDY_GUIDE: //教学指南
                log.debug("类型->教学指南");
                return "sStudyGuide";
            case CourseOverview.TEACHING_FORCUS: //重点难点
                log.debug("类型->重点难点");
                return "sTeachingForcus";
            case CourseOverview.REFERENCE: //参考资料目录
                log.debug("类型->参考资料目录");
                return "sReference";
            case CourseOverview.APPRAISAL: //考评方式与标准
                log.debug("类型->考评方式与标准");
                return "sAppraisal";
            case CourseOverview.DECLARATION: //申报材料
                log.debug("类型->申报材料");
                return "sDeclaration";
            default:
                log.debug("unknow type");
                log.warn("错误的概况类型:type->" + type);
                return null;
        }
    }

    /**
     * 判断概况文件下载字段
     * @param type 概况类型
     * @return String
     *
     * @author fanruiping
     * @since 2015-02-05
     */
    private String judgeDownloadFied(final int type) {
        switch (type) {
            case CourseOverview.ABSTRACT: //课程简介
                log.debug("类型->课程简介word下载地址");
                return "sAbstractDownloadUrl";
            case CourseOverview.TEACHING_OUTLINE: //教学大纲
                log.debug("类型->教学大纲word下载地址");
                return "sOutlineDownloadUrl";
            case CourseOverview.TEACHING_CALENDAR: //教学日历
                log.debug("类型->教学日历word下载地址");
                return "sCalendarDownlloadUrl";
            case CourseOverview.STUDY_GUIDE: //教学指南
                log.debug("类型->教学指南word下载地址");
                return "sGuideDownloadUrl";
            case CourseOverview.TEACHING_FORCUS: //重点难点
                log.debug("类型->重点难点word下载地址");
                return "sForcusDownloadUrl";
            case CourseOverview.REFERENCE: //参考资料目录
                log.debug("类型->参考资料目录word下载地址");
                return "sReferenceDownloadUrl";
            case CourseOverview.APPRAISAL: //考评方式与标准
                log.debug("类型->考评方式与标准word下载地址");
                return "sAppraisalDownloadUrl";
            case CourseOverview.DECLARATION: //申报材料
                log.debug("类型->申报材料word下载地址");
                return "sDeclarationDownloadUrl";
            default:
                log.debug("unknow type");
                log.warn("错误的概况类型:type->" + type);
                return null;
        }
    }

    /**
     * 判断概况文件显示字段
     * @param type 概况类型
     * @return String
     *
     * @author fanruiping
     * @since 2015-02-05
     */
    private String judgeShowFied(final int type) {
        switch (type) {
            case CourseOverview.ABSTRACT: //课程简介
                log.debug("类型->课程简介word显示地址");
                return "sAbstractShowUrl";
            case CourseOverview.TEACHING_OUTLINE: //教学大纲
                log.debug("类型->教学大纲word显示地址");
                return "sOutlineShowUrl";
            case CourseOverview.TEACHING_CALENDAR: //教学日历
                log.debug("类型->教学日历word显示地址");
                return "sCalendarShowUrl";
            case CourseOverview.STUDY_GUIDE: //教学指南
                log.debug("类型->教学指南word显示地址");
                return "sGuideShowUrl";
            case CourseOverview.TEACHING_FORCUS: //重点难点
                log.debug("类型->重点难点word显示地址");
                return "sForcusShowUrl";
            case CourseOverview.REFERENCE: //参考资料目录
                log.debug("类型->参考资料目录word显示地址");
                return "sReferenceShowUrl";
            case CourseOverview.APPRAISAL: //考评方式与标准
                log.debug("类型->考评方式与标准word显示地址");
                return "sAppraisalShowUrl";
            case CourseOverview.DECLARATION: //申报材料
                log.debug("类型->申报材料");
                return "sDeclarationShowUrl";
            default:
                log.debug("unknow type");
                log.warn("错误的概况类型:type->" + type);
                return null;
        }
    }
}
